﻿using System;
using System.Data;
using System.Data.SqlClient;
using UniversityStudentManagemnetSystem.DAO.DAL;

namespace UniversityStudentManagemnetSystem.DAO.Gateway
{
    public class CourseAssignGateway:DAO.Gateway.Gateway
    {
        public DataTable GetTeacherOf(int selectedCourseId)
        {
            Command.CommandText =
                @"SELECT t_Teacher.Name , t_Designation.Name AS Designation, t_Department.Name AS Department FROM t_CourseAssignToTeacher INNER JOIN t_Teacher ON t_CourseAssignToTeacher.TeacherId = t_Teacher.Id INNER JOIN t_Department ON t_Teacher.DepartmentId = t_Department.Id INNER JOIN t_Designation ON t_Teacher.DesignationId = t_Designation.Id WHERE t_CourseAssignToTeacher.CourseId = @id";
            Command.Parameters.Clear();
            Command.Parameters.Add("@id", SqlDbType.Int).Value = selectedCourseId;
            queryString = GetQueryString(Command);

            DataTable table;
            try
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, Connection.ConnectionString);
                dataAdapter.Fill(dataSet);
                table = dataSet.Tables[0];
                nbrOfTableRows = table.Rows.Count;
            }
            catch(FormatException exception)
                {
                    
                    throw new Exception("Input was not in Correct Type");
              }
            catch (Exception exception)
            {
                throw new Exception("Database Eroor", exception);
            }

            return table;
        }

        public DataTable GetAllOf(int selectedCourseId)
        {
            Command.CommandText = "SELECT CourseName, Credit FROM t_Course WHERE Id = @id";
            Command.Parameters.Clear();
            Command.Parameters.Add("@id", SqlDbType.Int).Value = selectedCourseId;
            queryString = GetQueryString(Command);

            DataTable table;
            try
            {
                DataSet dataSet = new DataSet();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(queryString, Connection.ConnectionString);
                dataAdapter.Fill(dataSet);
                table = dataSet.Tables[0];
                nbrOfTableRows = table.Rows.Count;
            }

            catch(SqlException sqlException)
            {
                throw new Exception("Database Problem. Please contact to Administration.",sqlException);
            }

            catch (Exception exception)
            {
                throw new Exception("Database Eroor", exception);
            }

            return table;
        }

        public bool SetCourseTo(Teacher aTeacher)
        {
            Command.CommandText = "INSERT INTO t_CourseAssignToTeacher(CourseId, TeacherId) VALUES (@courseId, @teacherId)";
            Command.Parameters.Clear();
            Command.Parameters.Add("@courseId", SqlDbType.Int).Value = aTeacher.Course.CourseId;
            Command.Parameters.Add("@teacherId", SqlDbType.Int).Value = aTeacher.Id;
            queryString = GetQueryString(Command);

            try
            {
                Connection.Open();
                nbrOfAffectedRows = Command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception("Database Error", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }


            if (nbrOfAffectedRows == (int)Rows.oneRowAffected)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
    }
}